/*
 * Copyright © 2008 PCCW Solutions All right reserved.
 *
 */
package com.blues.framework.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Savepoint;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Vector;
import java.util.List;
import java.util.Map;
import java.util.Hashtable;

import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.lang.ObjectUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.commons.lang.math.NumberUtils;
import org.apache.log4j.Logger;

import com.blues.framework.db.handlers.AtomTypeScalarHandler;

/**
 * 数据库JDBC操作辅助类
 * @author: Jones Wang in 2006-8-2 20:12:22.
 */
public class DbHelper {
    private static final Logger logger = Logger.getLogger(DbHelper.class);
    
	private static ThreadLocal sess = new ThreadLocal() {
         protected synchronized Object initialValue() {
             return new Session();
         }
     };

     public static Session getSession() {
         return (Session)sess.get();
     }

	/**
	 * 查询事务是否启动
	 */
	public boolean isTransactionStarted() {
		return getSession().isTransactionStarted();
	}

	/**
	 * 获取当前数据库连接对象，如果没有则从DataSource中取一个
	 * 需要调用closeConnection关闭连接
	 * @throws DbException 
	 */
	public Connection getConnection() throws DbException {
		Connection conn = null;
		try {
			conn = getSession().getConnection();
		} catch (SQLException exp) {
			throwDbException(-1, "", exp);
		}
		return conn;
	}

	/**
	 * 从DataSource中取一个新的数据库联接
     * 需要手动关闭连接 conn.close()
	 * @throws DbException 
	 */
	public Connection newConnection() throws DbException {
		Connection conn = null;
		try {
			conn = getSession().newConnection();
		} catch (SQLException exp) {
			throwDbException(-1, "", exp);
		}
		return conn;
	}

	/**
	 * 当没有事务时关闭数据库连接
	 * @throws DbException if a database access error occurs
	 */
	public void closeConnection() throws DbException {
		try {
			getSession().closeConnection();
		} catch (SQLException exp) {
			throwDbException(-1, "", exp);
		}
	}

	/**
	 * 启动事务模式
	 * @throws DbException if a database access error occurs
	 */
	public void startTransaction() throws DbException {
		try {
			getSession().startTransaction();
		} catch (SQLException exp) {
			throwDbException(-1, "", exp);
		}
	}

	/**
	 * 启动事务模式
	 * @param transactionIsolation 事务隔离级别，
	 * 以下 Connection 常量之一：Connection.TRANSACTION_READ_UNCOMMITTED、
	 *							 Connection.TRANSACTION_READ_COMMITTED、
	 *							 Connection.TRANSACTION_REPEATABLE_READ 或 Connection.TRANSACTION_SERIALIZABLE。
	 * （注意，不能使用 Connection.TRANSACTION_NONE，因为它指定了不受支持的事务。） 
	 * @throws DbException if a database access error occurs
	 */
	public void startTransaction(int transactionIsolation) throws DbException {
		try {
			getSession().startTransaction(transactionIsolation);
		} catch (SQLException exp) {
			throwDbException(-1, "", exp);
		}
	}

	/**
	 * 回滚事务
	 * @throws DbException if a database access error occurs or this
     *            <code>Connection</code> object is in auto-commit mode
	 */
	public void rollbackTransaction() throws DbException {
		try {
			getSession().rollbackTransaction();
		} catch (SQLException exp) {
			throwDbException(-1, "", exp);
		}
	}
	
	/**
	 * 提交事务
	 * @throws DbException if a database access error occurs or this
     *            <code>Connection</code> object is in auto-commit mode
	 */
	public void commitTransaction() throws DbException {
		try {
			getSession().commitTransaction();
		} catch (SQLException exp) {
			throwDbException(-1, "", exp);
		}
	}
	
	/**
	 * 回滚事务到保存点
	 * @throws DbException if a database access error occurs or this
     *            <code>Connection</code> object is in auto-commit mode
	 */
	public void rollbackTransaction(Savepoint point) throws DbException {
		try {
			getSession().rollbackTransaction(point);
		} catch (SQLException exp) {
			throwDbException(-1, "", exp);
		}
	}

	/**
	 * 结束事务
	 * @throws DbException if a database access error occurs
	 */
	public void endTransaction() throws DbException {
		try {
			getSession().endTransaction();
		} catch (SQLException exp) {
			throwDbException(-1, "", exp);
		}
	}
	

	/**
	 * 为当前事务状态设置一个匿名保存点
	 * @throws DbException if a database access error occurs or this
     *            <code>Connection</code> object is in auto-commit mode
	 */
	public Savepoint setTransactionSavePoint() throws DbException {
		Savepoint point = null;
		try {
			point = getSession().setTransactionSavePoint();
		} catch (SQLException exp) {
			throwDbException(-1, "", exp);
		}
		return point;
	}
	
	/**
	 * 为当前事务状态设置一个有名字保存点
	 * @throws DbException if a database access error occurs or this
     *            <code>Connection</code> object is in auto-commit mode
	 */
	public Savepoint setTransactionSavePoint(String name) throws DbException {
		Savepoint point = null;
		try {
			point = getSession().setTransactionSavePoint(name);
		} catch (SQLException exp) {
			throwDbException(-1, "", exp);
		}
		return point;
	}
	
	/**
	 * 释放事务保存点
	 * @throws DbException if a database access error occurs or
     *            the given <code>Savepoint</code> object is not a valid 
     *            savepoint in the current transaction
	 */
	public Savepoint releaseTransactionSavePoint(Savepoint point) throws DbException {
		try {
			point = getSession().releaseTransactionSavePoint(point);
		} catch (SQLException exp) {
			throwDbException(-1, "", exp);
		}
		return point;
	}
	

	// ========================  ProcCaller  ================================
	
	/**
     * 调用指定名称的存储过程，该存储过程只返回一个值，指明结果是否success
     *
     * @param procName 存储过程名
     * @return success then return true
	 * @throws DbException if a database access error occurs
     */
    public Object runProcScalar(String procName) throws DbException {
		return runProcScalar(procName, new Object[]{}, new ScalarHandler());
    }
    
	/**
     * 调用指定名称的存储过程，该存储过程只返回一个值，指明结果是否success
     *
     * @param procName 存储过程名
     * @param params   参数列表
     * @return success then return true
     * @throws DbException if a database access error occurs
     */
    public Object runProcScalar(String procName, ResultSetHandler rsh) throws DbException {
		return runProcScalar(procName, new Object[]{}, rsh);
    }

	/**
     * 调用指定名称的存储过程，该存储过程只返回一个值，指明结果是否success
     *
     * @param procName 存储过程名
     * @param params   参数列表
     * @return success then return true
     * @throws DbException if a database access error occurs
     */
    public Object runProcScalar(String procName, Object[] params) throws DbException {
        return runProcScalar(procName, params, new ScalarHandler());
    }
    
    /**
     * 调用指定名称的存储过程，该存储过程只返回一个值，指明结果是否success
     * @deprecated by Kyle.Zhang 2010-5-21 13:33:31, <br/>
	 *			the schema replaced by {@link com.pccw.commons.database.DbException} throws by DbHelper. 
     *
     * @param procName 存储过程名
     * @param params   参数列表
     * @return success then return true
     * @throws DbException if a database access error occurs
     */
    public Object runProcScalar(String procName, Object[] params, ResultSetHandler rsh) 
    	throws DbException { 
    	logger.debug(">>>runProcScalar(procName: " + procName + ", params, rsh)");

    	outputParam("runProcScalar", params);

		Object result = null;
		int dbFlag = -1;
		String dbMessage = "";
		
	        try {
				ProcCaller run = new ProcCaller(getSession().getConnection());
	            result = run.query(procName, params, rsh);
				
				dbFlag = run.getFlag();
				dbMessage = run.getMsg();
	        } catch (SQLException exp) {
	            throwDbException(dbFlag, dbMessage, exp);
	        } finally {
	        	this.closeConnection();
	        }
		
		// 业务逻辑错误则抛出异常
		if (dbFlag == 101) {
			throwDbException(dbFlag, dbMessage);
		}

		logger.debug("<<<runProcScalar(procName: " + procName  + ", params, rsh): " + result);

		return result;
    }

    /**
     * 调用指定名称的存储过程，该存储过程只返回一个值，指明结果是否success
     *
     * @param procName 存储过程名
     * @return success then return true
	 * @throws DbException if a database access error occurs
     */
    public Object runProcScalarAtom(String procName) throws DbException {
		return runProcScalarAtom(procName, new Object[]{});
    }
    /**
     * 调用指定名称的存储过程，该存储过程只返回一个值，指明结果是否success
     *
     * @param procName 存储过程名
     * @param params   参数列表
     * @return success then return true
     * @throws DbException if a database access error occurs
     */
    public Object runProcScalarAtom(String procName, Object[] params) throws DbException {
        logger.debug(">>>runProcScalarAtom(procName: " + procName + ", params)");

    	outputParam("runProcScalarAtom", params);

		ResultSetHandler rsh = new AtomTypeScalarHandler();
    	Object result = runProcScalar(procName, params, rsh);
			
		logger.debug("<<<runProcScalarAtom(procName: " + procName  + ", params): " + result);
		return result;
    }

	
	/**
     * 调用指定名称的存储过程，该存储过程返回记录列表，指明结果是否success
     *
     * @param procName 存储过程名
	 * @param rsh	   结果处理类
     * @return success then return true
	 * @throws DbException if a database access error occurs
     */
    public List runProcQuery(String procName, ResultSetHandler rsh) throws DbException {
        return runProcQuery(procName, new Object[]{}, rsh);
    }

    /**
     * 调用指定名称的存储过程，该存储过程返回记录列表，指明结果是否success
     * @deprecated by Kyle.Zhang 2010-5-21 13:33:31, <br/>
	 *			the schema replaced by {@link com.pccw.commons.database.DbException} throws by DbHelper. 
     *
     * @param procName 存储过程名
     * @param params   参数列表
	 * @param rsh	   结果处理类
     * @return success then return true
	 * @throws DbException if a database access error occurs
     */
    public List runProcQuery(String procName, Object[] params, ResultSetHandler rsh) 
    	throws DbException {
		logger.debug(">>>runProcQuery(procName: " + procName + ", params, rsh)");
//		for(int i=0;i<params.length;i++){
//			logger.debug(">>>runProcQuery["+i+"] = "+params[i]);
//		}
    	outputParam("runProcQuery", params);
		List result = null;
		int dbFlag = -1;
		String dbMessage = "";
		
		
	        try {
				ProcCaller run = new ProcCaller(getSession().getConnection());
	        	result = (List)run.query(procName, params, rsh);
	            if (result == null) {
	    			result = new ArrayList();
	    		}
				dbFlag = run.getFlag();
				dbMessage = run.getMsg();
	        } catch (SQLException exp) {
	            throwDbException(dbFlag, dbMessage, exp);
	        } finally {
	        	this.closeConnection();
	        }
		
		
		// 业务逻辑错误则抛出异常
		if (dbFlag == 101) {
			throwDbException(dbFlag, dbMessage);
		}

		
		logger.debug("<<<runProcQuery(procName: " + procName + ", params, rsh): " + result.size());
		
		return result;
    }
    
    
    /**
     * 根据设定参数运行存储函数，并将结果以字符串方式返回
     *
     * @param funcName 执行的存储函数名称
	 * @return 函数返回值
	 * @throws DbException if a database access error occurs
     */
    public Object callFunction(String funcName) throws DbException {
    	return this.callFunction(funcName, new Object[]{});
    }
    
	/**
     * 根据设定参数运行存储函数，并将结果以字符串方式返回
     *
     * @param funcName 执行的存储函数名称
	 * @param params 参数数组
	 * @return 函数返回值
	 * @throws DbException if a database access error occurs
     */
    public Object callFunction(String funcName, Object[] params) throws DbException {
		logger.debug(">>>callFunction(funcName:" + funcName + ", params)");
		outputParam("callFunction", params);

        Object result = null;
        try {
			ProcCaller run = new ProcCaller(getSession().getConnection());
			
            result = run.callFunc(funcName, params);
        } catch (SQLException exp) {
            throwDbException(-1, "", exp);
        } finally {
        	this.closeConnection();
        }

		logger.debug("<<<callFunction(funcName:" + funcName + ", params): " + result);
		return result;
    }


    // ========================  QueryCaller  ================================
    /**
     * 调用SQL返回记录列表
     * @param sql 查询语句
	 * @param rsh	   结果处理类
     * @return success then return true
	 * @throws DbException if a database access error occurs
     */
    public List runSQLQuery(String sql) throws DbException {
        return runSQLQuery(sql, new Object[]{});
    }
	
	/**
     * 调用SQL返回记录列表
     * @param sql 查询语句
	 * @param rsh	   结果处理类
     * @return success then return true
	 * @throws DbException if a database access error occurs
     */
    public List runSQLQuery(String sql, Object[] params) throws DbException {
        return runSQLQuery(sql, params, new org.apache.commons.dbutils.handlers.MapListHandler());
    }
	
	
	/**
     * 调用SQL返回记录列表
     * @param sql 查询语句
	 * @param rsh	   结果处理类
     * @return success then return true
	 * @throws DbException if a database access error occurs
     */
    public List runSQLQuery(String sql, ResultSetHandler rsh) throws DbException {
        return runSQLQuery(sql, new Object[]{}, rsh);
    }
	/**
     * 调用SQL返回记录列表
     * @param sql 查询语句
	 * @param params   参数列表
	 * @param rsh	   结果处理类
     * @return success then return true
	 * @throws DbException if a database access error occurs
     */
    public List runSQLQuery(String sql, Object[] params, ResultSetHandler rsh) 
    	throws DbException {
		logger.debug(">>>runSQLQuery(sql:" + sql + ", params, rsh)");
		outputParam("runSQLQuery", params);

		List result = null;
		try {
	        QueryCaller run = new QueryCaller(getSession().getConnection());
			result = (List)run.query(sql, params, rsh);
			if (result == null) {
				result = new ArrayList();
			}
		} catch (SQLException exp) {
            throwDbException(-1, "", exp);
		} finally {
        	this.closeConnection();
        }

		logger.debug("<<<runSQLQuery(sql, params, rsh): " + result.size());
		return result;
    }
	
	/**
     * 调用SQL返回一个值，指明结果是否success
     *
     * @param sql 查询语句
     * @return success then return true
	 * @throws DbException if a database access error occurs
     */
    public Object runSQLScalar(String sql) throws DbException {
		ResultSetHandler rsh = new ScalarHandler();

        return runSQLScalar(sql, new Object[]{}, rsh);
    }

	/**
     * 调用SQL返回一个值，指明结果是否success
     *
     * @param sql 查询语句
	 * @param rsh	   结果处理类
     * @return success then return true
	 * @throws DbException if a database access error occurs
     */
    public Object runSQLScalar(String sql, ResultSetHandler rsh) throws DbException {
		return runSQLScalar(sql, new Object[]{}, rsh);
    }
    
	/**
     * 调用SQL返回一个值，指明结果是否success
     *
     * @param sql 查询语句
     * @param params   参数列表
     * @return success then return true
     * @throws DbException if a database access error occurs
     */
    public Object runSQLScalar(String sql, Object[] params) throws DbException {
		ResultSetHandler rsh = new ScalarHandler();

        return runSQLScalar(sql, params, rsh);
    }

	/**
     * 调用SQL返回一个值，指明结果是否success
     *
     * @param sql 查询语句
     * @param params   参数列表
	 * @param rsh	   结果处理类
     * @return success then return true
     * @throws DbException if a database access error occurs
     */
    public Object runSQLScalar(String sql, Object[] params, ResultSetHandler rsh) throws DbException {
		logger.debug(">>>runSQLQuery(sql:" + sql + ", params, rsh)");
		outputParam("runSQLScalar", params);

        Object result = null;
        try {
	        QueryCaller run = new QueryCaller(getSession().getConnection());
            result = run.query(sql, params, rsh);
        } catch (SQLException exp) {
            throwDbException(-1, "", exp);
        } finally {
        	this.closeConnection();
        }

		logger.debug("<<<runSQLQuery(sql:" + sql + ", params, rsh): " + result);
        return result;
    }

	/**
     * 调用SQL返回一个值，指明结果是否success
     *
     * @param sql 查询语句
     * @return success then return true
	 * @throws DbException if a database access error occurs
     */
    public Object runSQLScalarAtom(String sql) throws DbException {
		logger.debug(">>>runSQLScalarAtom(sql:" + sql + ")");

		ResultSetHandler rsh = new AtomTypeScalarHandler();

        Object result = null;
        try {
			QueryCaller run = new QueryCaller(getSession().getConnection());
            result = run.query(sql, rsh);
        } catch (SQLException exp) {
            throwDbException(-1, "", exp);
        } finally {
        	this.closeConnection();
        }
		
		logger.debug("<<<runSQLScalarAtom(sql:" + sql + "): " + result);
        return result;
    }
    /**
     * 调用SQL返回一个值，指明结果是否success
     *
     * @param sql 查询语句
     * @param params   参数列表
     * @return success then return true
     * @throws DbException if a database access error occurs
     */
    public Object runSQLScalarAtom(String sql, Object[] params) throws DbException {
		logger.debug(">>>runSQLScalarAtom(sql:" + sql + ", params)");
		outputParam("runSQLScalarAtom", params);
        ResultSetHandler rsh = new AtomTypeScalarHandler();

        Object result = null;
        try {
	        QueryCaller run = new QueryCaller(getSession().getConnection());
            result = run.query(sql, params, rsh);
        } catch (SQLException exp) {
            throwDbException(-1, "", exp);
        } finally {
        	this.closeConnection();
        }
		
		logger.debug("<<<runSQLScalarAtom(sql:" + sql + ", params): " + result);
        return result;
    }

	/**
     * 调用SQL更新数据库
     *
     * @param sql 更新语句
     * @return 更新记录条数
	 * @throws DbException if a database access error occurs
     */
    public int runSQLUpdate(String sql) throws DbException {
        return runSQLUpdate(sql, new Object[]{});
    }

	/**
	 * 调用SQL更新数据库
	 * 
	 * @param sql 更新语句
	 * @param params 参数列表
	 * @return 更新记录条数
	 * @throws DbException if a database access error occurs
	 */
	public int runSQLUpdate(String sql, Object[] params) throws DbException {
		logger.debug(">>>runSQLUpdate(sql:" + sql + ", params)");
		outputParam("runSQLUpdate", params);
		int result = 0;

		try {
			QueryCaller run = new QueryCaller(getSession().getConnection());
			result = run.update(sql, params);
		} catch (SQLException exp) {
			throwDbException(-1, "", exp);
		} finally {
			this.closeConnection();
		}

		logger.debug("<<<runSQLUpdate(sql:" + sql + ", params): " + result);
		return result;
	}

	/** 批执行脚本集合 */
	private static Hashtable _batchScripts = null;
	/** 批执行脚本缓存参数集合 */
	private static Hashtable _batchParameters = new Hashtable();

	/**
	 * 装载批处理脚本
	 */
	private static synchronized void loadBatchScripts() {
		if (_batchScripts == null) {
			_batchScripts = new Hashtable();

			ResultSetHandler rsh = new MapListHandler();
			try {
				List info = new DbHelper().runSQLQuery(BatchSQL.QUERY_CONFIG_SQL, rsh);

				for (int idx = 0; idx < info.size(); idx ++) {
					BatchSQL batch = new BatchSQL((Map)info.get(idx));
					_batchScripts.put(batch.getName(), batch);
				}
			} catch (DbException e) {
				logger.error(e, e);
			}
		}
	}

	/**
     * 调用SQL更新数据库
     *
     * @param batchName 批处理脚本名称
     * @return 更新记录条数
	 * @throws DbException if a database access error occurs
     */
    public void runSQLBatchUpdate(String batchName) throws DbException {
        runSQLBatchUpdate(batchName, new Object[]{});
    }

	/**
	 * 调用SQL更新数据库
	 * 
	 * @param batchName 批处理脚本名称
	 * @param params 参数列表
	 * @return 更新记录条数
	 * @throws DbException if a database access error occurs
	 */
	public void runSQLBatchUpdate(String batchName, Object[] params) throws DbException {
		logger.debug(">>>runSQLBatchUpdate(batchName, params)");
		logger.debug("|->batchName: " + batchName);
		outputParam("runSQLBatchUpdate", params);

		loadBatchScripts();

		BatchSQL batch = (BatchSQL)_batchScripts.get(batchName);
		Vector vector = (Vector)_batchParameters.get(batchName);
		if (batch != null) {
			if (vector == null) {
				vector = new Vector();
				_batchParameters.put(batchName, vector);
			}
			vector.add(params);

			if (vector.size() >= batch.getBatchCount()) {
				_batchParameters.put(batchName, new Vector());

				Connection conn = null;
				PreparedStatement stmt = null;
				try {
					conn = newConnection();
					conn.setAutoCommit(false);
					if (StringUtils.equals(batch.getType(), "PROC-PARAM")) {
						stmt = conn.prepareCall(batch.getSql());
					} else {
						stmt = conn.prepareStatement(batch.getSql());
					}
					
					for (int idx = 0; idx < vector.size(); idx ++) {
						fillProc(stmt, (Object[])vector.get(idx));
						stmt.addBatch();
					}
					int count[] = stmt.executeBatch();
					conn.commit();
					conn.close();
					batch.setLastTimeMillis(System.currentTimeMillis());
					logger.debug("batch.getLastTimeMillis(): " + batch.getLastTimeMillis() + ", vector.size(): " + vector.size());
				} catch (SQLException exp) {
					try {
						conn.rollback();
					} catch (SQLException e) {
					}
					throwDbException(-1, "", exp);
				} finally {
					DbUtils.closeQuietly(stmt);
					DbUtils.closeQuietly(conn);
				}
			}
		}
		logger.debug("<<<runSQLBatchUpdate(batchName, params)");
		//return result;
	}

	private void fillProc(PreparedStatement stmt, Object[] params)
            throws SQLException {

        if (params == null) {
            return;
        }

        for (int i = 0; i < params.length; i++) {
			logger.debug("params[" + i + "] = " + ObjectUtils.toString(params[i], "<null>"));
			stmt.setObject(i + 1, params[i]);
		}

		/*int paramLen = params.length;
		// 传入3个固定传出参数
        stmt.registerOutParameter(paramLen + 1, Types.NUMERIC);  // return flag
        stmt.registerOutParameter(paramLen + 2, Types.VARCHAR);  // return message
        stmt.registerOutParameter(paramLen + 3, -10);            // return rs*/
    }


	/**
	 * 执行无参存储过程
	 * 
	 * @param conn 数据库连接
	 * @param procName 存储过程名
	 * @return 结果集
	 */
	public ResultSet executeProc(Connection conn, String procName) throws DbException {
		return executeProc(conn, procName, new Object[] {});
	}

	/**
	 * 执行有参存储过程
	 * 
	 * @param conn 数据库连接
	 * @param procName 存储过程名
	 * @param params 参数数组
	 * @return 结果集
	 */
	public ResultSet executeProc(Connection conn, String procName, Object[] params) throws DbException {
		logger.debug(">>>executeProc(procName:" + procName + ", params)");
		outputParam("executeProc", params);
		ProcCaller run = new ProcCaller(conn);

		ResultSet result = null;
		int dbFlag = -1;
		String dbMessage = "";

		try {
			result = run.executeProc(conn, procName, params);
			dbFlag = run.getFlag();
			dbMessage = run.getMsg();
		} catch (SQLException exp) {
			throwDbException(dbFlag, dbMessage, exp);
		}

		// 业务逻辑错误则抛出异常
		if (dbFlag == 101) {
			throwDbException(dbFlag, dbMessage);
		}

		logger.debug("<<<executeProc(procName:" + procName + ", params): " + result);
		return result;
	}

	/**
	 * 执行无参sql
	 * 
	 * @param conn 数据库连接
	 * @param procName sql
	 * @return 结果集
	 */
	public ResultSet executeSQL(Connection conn, String sql) throws DbException {
		return executeSQL(conn, sql, new Object[] {});
	}

	/**
	 * 执行有参sql
	 * 
	 * @param conn 数据库连接
	 * @param procName sql
	 * @param params 参数数组
	 * @return 结果集
	 */
	public ResultSet executeSQL(Connection conn, String sql, Object[] params) throws DbException {
		logger.debug(">>>executeSQL(sql:" + sql + ", params)");
		outputParam("executeSQL", params);
		QueryCaller run = new QueryCaller(conn);
		ResultSet result = null;
		try {
			result = run.executeSQL(conn, sql, params);
		} catch (SQLException exp) {
			throwDbException(-1, "", exp);
		}
		logger.debug("<<<executeProc(sql:" + sql + ", params): " + result);
		return result;
	}

	/**
	 * 输出参数日志
	 * @param methodName 方法名称
	 * @param params 方法参数组
	 */
	protected void outputParam(String methodName, Object[] params) {
		if (logger.isDebugEnabled()) {
			for (int i = 0; i < params.length; i++) {
				logger.debug("|->" + methodName + "[" + i + "] = " + ObjectUtils.toString(params[i], "<null>"));
			}
		}
	}

	/**
	 * 获取序列当前值
	 * 
	 * @param sequenceName 序列名称
	 * @return 返回序列当前值
	 */
	public long sequenceThis(String sequenceName) {
		logger.debug(">>>sequenceThis(sequenceName: " + sequenceName + ")");
		long seqValue = 0;
		String sqlText = "select " + sequenceName + ".currval from dual";
		try {
			seqValue = NumberUtils.toLong((String) runSQLScalarAtom(sqlText));
		} catch (DbException exp) {
			logger.error(exp, exp);
		}

		logger.debug("<<<sequenceThis(sequenceName: " + sequenceName + ")): " + seqValue);
		return seqValue;
	}

	/**
	 * 获取序列下一值
	 * 
	 * @param sequenceName 序列名称
	 * @return 返回序列下一值
	 */
	public long sequenceNext(String sequenceName) {
		logger.debug(">>>sequenceNext(sequenceName: " + sequenceName + ")");
		long seqValue = 0;
		String sqlText = "select " + sequenceName + ".nextval from dual";
		try {
			seqValue = NumberUtils.toLong((String) runSQLScalarAtom(sqlText));
		} catch (DbException exp) {
			logger.error(exp, exp);
		}

		logger.debug("<<<sequenceNext(sequenceName: " + sequenceName + ")): " + seqValue);
		return seqValue;
	}

	/**
	 * 抛出DbHelper异常信息
	 * 
	 * @param dbFlag 存储过程返回值标识
	 * @param dbMessage 存储过程返回信息
	 */
	public static void throwDbException(int dbFlag, String dbMessage) throws DbException {
		throwDbException(dbFlag, dbMessage, null);
	}

	/**
	 * 抛出DbHelper异常信息
	 * 
	 * @param exp 原始异常信息
	 */
	public static void throwDbException(Exception exp) throws DbException {
		throwDbException(-1, "", exp);
	}

	/**
	 * 抛出DbHelper异常信息
	 * @param dbFlag 存储过程返回值标识
	 * @param dbMessage 存储过程返回信息
	 * @param exp 原始异常信息
	 */
	public static void throwDbException(int dbFlag, String dbMessage, Exception exp) throws DbException {
		if (StringUtils.isBlank(dbMessage) && exp != null) {
			dbMessage = exp.getMessage();
		}
		exp = new DbException(dbFlag, dbMessage, exp);
		logger.error(exp, exp);
		throw (DbException) exp;
	}
}
